﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

using Acl.Data.Mappings;
using Acl.Data.RowReaders;
using Acl.Data.RowWriters;
using Acl.Data.TableReaders;
using Acl.Data.TableWriters;
using Acl.Dynamic;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Acl.Excel
{
    /// <summary>
    /// Excel文件操作类
    /// </summary>
    public sealed class Workbook
    {
        internal IWorkbook InnerWorkbook;

        /// <summary>
        /// 获取Excel文件名
        /// </summary>
        public string FileName { get; private set; }

        /// <summary>
        /// Sheet 名称列表
        /// </summary>
        public string[] SheetNames
        {
            get
            {
                string[] sheetNames = new string[InnerWorkbook.NumberOfSheets];
                for (var i = 0; i < sheetNames.Length; i++)
                    sheetNames[i] = InnerWorkbook.GetSheetName(i);
                return sheetNames;
            }
        }

        /// <summary>
        /// 以文件流方式创建Excel操作类对象
        /// </summary>
        /// <param name="stream">文件流</param>
        public Workbook(FileStream stream)
        {
            Guard.NotNull(stream, "stream");
            FileName = stream.Name;
            InnerWorkbook = WorkbookFactory.Create(stream);
        }

        /// <summary>
        /// 以文件名方式创建Excel操作类对象
        /// </summary>
        /// <param name="excelFileName">文件名</param>
        public Workbook(string excelFileName)
        {
            Guard.NotNullOrEmpty(excelFileName, nameof(excelFileName));
            FileName = excelFileName;
            InnerWorkbook = WorkbookFactory.Create(excelFileName);
        }

        /// <summary>
        /// 重命名sheet 名字
        /// </summary>
        /// <param name="oldSheetName"></param>
        /// <param name="newSheetName"></param>
        public void RenameSheetName(string oldSheetName, string newSheetName)
        {
            Guard.NotNullOrEmpty(oldSheetName, "oldSheetName");
            Guard.NotNullOrEmpty(newSheetName, "newSheetName");

            var index = InnerWorkbook.GetSheetIndex(oldSheetName);
            if (index == -1)
                throw new Exception(string.Format("sheet :{0} 不存在，不能重命名", oldSheetName));

            InnerWorkbook.SetSheetName(index, newSheetName);
        }

        /// <summary>
        /// 删除sheet
        /// </summary>
        /// <param name="sheetName"></param>
        public void RemoveSheet(string sheetName)
        {
            Guard.NotNullOrEmpty(sheetName, "sheetName");

            var index = InnerWorkbook.GetSheetIndex(sheetName);
            if (index == -1)
                throw new Exception(string.Format("不能删除一个不存在的 sheet :{0} ", sheetName));

            InnerWorkbook.RemoveSheetAt(index);
        }

        /// <summary>
        /// 加载工作薄中内容到动态实体集合中
        /// </summary>
        /// <param name="entitySet">实体集合</param>
        public void Fill(DynamicEntitySet entitySet)
        {
            Fill(entitySet, new ExcelQueryOptions { SheetName = entitySet.EntityName });
        }

        /// <summary>
        /// 加载工作薄中内容到动态实体集合中
        /// </summary>
        /// <param name="entitySet">实体集合</param>
        /// <param name="options"></param>
        public void Fill(DynamicEntitySet entitySet, ExcelQueryOptions options)
        {
            Guard.NotNull(entitySet, nameof(entitySet));
            Guard.NotNull(options, nameof(options));

            if (options.ColumnSize == 0)
                throw new ArgumentNullException("columnSize must be greater thran 0.");
            if (options.StartRowNumber == 0)
                throw new ArgumentNullException("startRowNumber must be greater thran 0.");
            if (options.StartColumnNumber == 0)
                throw new ArgumentNullException("startColumnNumber must be greater thran 0.");
            var worksheet = InnerWorkbook.GetSheet(options.SheetName);

            if (worksheet == null)
                throw new ArgumentException(string.Format("{0} sheet in {1} workbook not exists.", options.SheetName, FileName));

            var rowReader = new ExcelRowReader(this.FileName, worksheet, options.StartRowNumber, options.StartColumnNumber, options.ColumnSize, (int?)options.RowSize);
            var reader = new DynamicTableLoader(rowReader) { ColumnSize = (int?)options.ColumnSize, RowSize = (int?)options.RowSize };

            reader.Fill(entitySet);
        }

        /// <summary>
        /// 加载工作薄中内容到动态实体集合中
        /// </summary>
        /// <param name="models">实体集合</param>
        /// <param name="sheetName"></param>
        public void Fill<TModel>(IList<TModel> models, string sheetName)
            where TModel : new()
        {
            Fill(models, new ExcelQueryOptions { SheetName = sheetName });
        }

        /// <summary>
        /// 加载工作薄中内容到动态实体集合中
        /// </summary>
        /// <param name="models">实体集合</param>
        /// <param name="options"></param>
        public void Fill<TModel>(IList<TModel> models, ExcelQueryOptions options)
            where TModel : new()
        {
            Guard.NotNull(models, nameof(models));
            Guard.NotNull(options, nameof(options));

            if (options.ColumnSize == 0)
                throw new ArgumentNullException("columnSize must be greater thran 0.");
            if (options.StartRowNumber == 0)
                throw new ArgumentNullException("startRowNumber must be greater thran 0.");
            if (options.StartColumnNumber == 0)
                throw new ArgumentNullException("startColumnNumber must be greater thran 0.");
            var worksheet = InnerWorkbook.GetSheet(options.SheetName);

            if (worksheet == null)
                throw new ArgumentException(string.Format("{0} sheet in {1} workbook not exists.", options.SheetName, FileName));

            var builder = options.MappingBuilder;
            if (builder == null)
            {
                builder = ColumnMappingBuilder.CreateColumnNameMappingBuilder<TModel>() as ColumnMappingBuilder;
                options.HasHeader = true;
            }

            var rowReader = new ExcelRowReader(this.FileName, worksheet, options.StartRowNumber, options.StartColumnNumber, options.ColumnSize, (int?)options.RowSize);
            var loader = new TableReader<TModel>(rowReader, builder)
            {
                HasHeader = options.HasHeader,
                ColumnSize = (int?)options.ColumnSize,
                RowSize = (int?)options.RowSize
            };

            loader.Fill(models);
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DynamicEntitySet Read(string sheetName)
        {
            return Read(new ExcelQueryOptions { SheetName = sheetName });
        }

        /// <summary>
        /// 查询工作薄中内容，返回动态实体集合
        /// </summary>
        /// <param name="options"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="ArgumentException"></exception>
        public DynamicEntitySet Read(ExcelQueryOptions options)
        {
            Guard.NotNull(options, "options");
            Guard.NotNull(options.SheetName, "sheetName");

            if (options.ColumnSize == 0)
                throw new ArgumentNullException("columnSize must be greater thran 0.");
            if (options.StartRowNumber == 0)
                throw new ArgumentNullException("startRowNumber must be greater thran 0.");
            if (options.StartColumnNumber == 0)
                throw new ArgumentNullException("startColumnNumber must be greater thran 0.");
            var worksheet = InnerWorkbook.GetSheet(options.SheetName);

            if (worksheet == null)
                throw new ArgumentException(string.Format("{0} sheet in {1} workbook not exists.", options.SheetName, FileName));

            var rowReader = new ExcelRowReader(this.FileName, worksheet, options.StartRowNumber, options.StartColumnNumber, options.ColumnSize, (int?)options.RowSize);
            var reader = new DynamicTableReader(rowReader) { ColumnSize = (int?)options.ColumnSize, RowSize = (int?)options.RowSize, HasHeader = options.HasHeader };

            var table = reader.Read();
            table.EntityName = options.SheetName;

            return table;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public IEnumerable<TModel> Read<TModel>(string sheetName) where TModel : new()
        {
            return Read<TModel>(new ExcelQueryOptions { SheetName = sheetName });
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="options"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="ArgumentException"></exception>
        public IEnumerable<TModel> Read<TModel>(ExcelQueryOptions options) where TModel : new()
        {
            var sheetName = options.SheetName ?? typeof(TModel).Name;
            if (options.ColumnSize == 0)
                throw new ArgumentNullException("columnSize must be greater thran 0.");
            if (options.StartRowNumber == 0)
                throw new ArgumentNullException("startRowNumber must be greater thran 0.");
            if (options.StartColumnNumber == 0)
                throw new ArgumentNullException("startColumnNumber must be greater thran 0.");

            var worksheet = InnerWorkbook.GetSheet(sheetName);

            if (worksheet == null)
                throw new ArgumentException(string.Format("{0} sheet in {1} workbook not exists.", sheetName, FileName));

            var rowReader = new ExcelRowReader(this.FileName, worksheet, options.StartRowNumber, options.StartColumnNumber, options.ColumnSize, (int?)options.RowSize);

            var builder = options.MappingBuilder;
            builder ??= ColumnMappingBuilder.CreateColumnNameMappingBuilder<TModel>() as ColumnMappingBuilder;

            var reader = new TableReader<TModel>(rowReader, builder)
            {
                HasHeader = options.HasHeader,//  onColumnMappingHandler is IColumnNameMappingBuilder<TModel>,
                ColumnSize = (int?)options.ColumnSize,
                RowSize = (int?)options.RowSize,
            };

            return reader.Read();
        }

        //设置单元格格式函数，边框粗细3个可选
        internal static ICellStyle GetCellStyle(IWorkbook workbook, int borderThickness, bool isDate = false)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            var borderType = borderThickness switch
            {
                0 => BorderStyle.Thin,
                1 => BorderStyle.Medium,
                2 => BorderStyle.Thick,
                _ => BorderStyle.Thin,
            };
            cellStyle.BorderBottom = borderType;
            cellStyle.BorderTop = borderType;
            cellStyle.BorderLeft = borderType;
            cellStyle.BorderRight = borderType;

            IFont font = workbook.CreateFont();//设置字体大小和颜色
            font.FontName = "宋体";
            font.FontHeightInPoints = 13;

            if (borderType == BorderStyle.Thick)
                font.IsBold = true;

            cellStyle.SetFont(font);

            if (isDate)
            {
                IDataFormat format = workbook.CreateDataFormat();
                cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
            }
            return cellStyle;
        }

        /// <summary>
        /// 向工作薄写入内容，覆盖工作薄中原有内容
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="models"></param>
        /// <param name="sheetName"></param>
        /// <param name="builder"></param>
        /// <param name="append"></param>
        public void Write<TModel>(
            IEnumerable<TModel> models,
            string sheetName = null,
            ColumnMappingBuilder builder = null,
            bool append = false)
        {
            Guard.NotNull(models, "models");

            sheetName = sheetName ?? typeof(TModel).Name;
            builder = builder ?? ColumnMappingBuilder.CreateColumnNameMappingBuilder<TModel>() as ColumnMappingBuilder;

            uint startRowNumber = 1;
            ISheet worksheet = null;
            var sheetCount = (InnerWorkbook as dynamic).Count;
            if (sheetCount > 0)
            {
                var sheetIndex = InnerWorkbook.GetSheetIndex(sheetName);
                if (sheetIndex != -1)
                {
                    if (append)
                    {
                        var sourceEntitySet = Read(new ExcelQueryOptions { SheetName = sheetName });
                        startRowNumber = (uint)sourceEntitySet.Count + 1;
                        worksheet = InnerWorkbook.GetSheet(sheetName);
                    }
                    else//覆盖
                        InnerWorkbook.RemoveSheetAt(sheetIndex);
                }
            }

            worksheet ??= InnerWorkbook.CreateSheet(sheetName);

            uint startColumnNumber = 1;
            var includeHeader = startRowNumber == 1;

            var rowWriter = new ExcelRowWriter(this.FileName, worksheet, startRowNumber, startColumnNumber, includeHeader);
            var tableWriter = new TableWriter<TModel>(rowWriter, builder as ColumnMappingBuilder);

            tableWriter.Write(models, includeHeader);

            using (var stream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write))
            {
                InnerWorkbook.Write(stream);
            }
        }

        /// <summary>
        /// 向sheet 中写入内容，如果append = true,是追加内容，否则是覆盖内容,默认覆盖原有内容
        /// </summary>
        /// <param name="entitySet"></param>
        /// <param name="sheetName"></param>
        /// <param name="append"></param>
        public void Write(DynamicEntitySet entitySet, string sheetName = null, bool append = false)
        {
            Guard.NotNull(entitySet, "models");

            sheetName = sheetName ?? entitySet.EntityName;

            uint startRowNumber = 1;
            ISheet worksheet = null;
            var sheetCount = (InnerWorkbook as dynamic).Count;

            if (sheetCount > 0)
            {
                var sheetIndex = InnerWorkbook.GetSheetIndex(sheetName);

                if (sheetIndex != -1) //append
                {
                    if (append)
                    {
                        var sourceEntitySet = Read(new ExcelQueryOptions { SheetName = sheetName });
                        startRowNumber = (uint)sourceEntitySet.Count + 1;
                        worksheet = InnerWorkbook.GetSheet(sheetName);
                    }
                    else//覆盖
                    {
                        InnerWorkbook.RemoveSheetAt(sheetIndex);
                    }
                }
            }

            string[] header = null;
            if (startRowNumber == 1)
            {
                header = entitySet.Members.Select(p => p.Name).ToArray();
            }

            worksheet ??= InnerWorkbook.CreateSheet(sheetName);

            uint startColumnNumber = 1;
            var rowWriter = new ExcelRowWriter(this.FileName, worksheet, startRowNumber, startColumnNumber, header != null);
            var tableWriter = new DynamicTableWriter(rowWriter);
            tableWriter.Write(entitySet, header);
        }

        /// <summary>
        /// 向工作薄写入内容，如果append = true,是追加内容，否则是覆盖内容,默认覆盖原有内容
        /// </summary>
        /// <param name="entitySets"></param>
        /// <param name="append"></param>
        public void Write(DynamicEntitySetCollection entitySets, bool append = false)
        {
            Guard.NotNull(entitySets, "entitySets");

            foreach (var entitySet in entitySets)
            {
                Write(entitySet, entitySet.EntityName, append);
            }
        }

        /// <summary>
        /// 保存Excel文件
        /// </summary>
        public void Save()
        {
            using (var stream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write))
            {
                InnerWorkbook.Write(stream);
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="fileName"></param>
        public void SaveAs(string fileName)
        {
            Guard.NotNullOrEmpty(fileName, "fileName");
            using (var stream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
            {
                InnerWorkbook.Write(stream);
            }
        }

        private class WorkbookFactory
        {
            public static IWorkbook Create(Stream stream)
            {
                string version = string.Format("{0}{1}", stream.ReadByte().ToString(), stream.ReadByte().ToString());
                stream.Seek(0, SeekOrigin.Begin);
                return version switch
                {
                    //xls.doc.ppt
                    "208207" => new HSSFWorkbook(stream),
                    //xlsx,zip,pptx,mmap,zip
                    "8075" => new XSSFWorkbook(stream),
                    _ => throw new ArgumentException("file type error."),
                };
            }

            public static IWorkbook Create(string fileName)
            {
                if (File.Exists(fileName))
                {
                    using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                    {
                        return Create(stream);
                    }
                }

                var isNewVersion = fileName.LastIndexOf(".xlsx", StringComparison.InvariantCultureIgnoreCase) != -1;

                if (isNewVersion)
                {
                    return new XSSFWorkbook();
                }

                return new HSSFWorkbook();
            }
        }
    }
}